package com.kcs.dc.report.model.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.LogWriter;
import com.kcs.dc.report.model.dao.model.settleInfoModel;

public class settlementDAO {
	
	public settleInfoModel[] getInfoDeals(String deal_id, String ibc, String cust_name, 
			String ccy, String cif, String settle_flag, String dateFrom, String dateTo, Connection conn) throws SQLException {
		
		CallableStatement proc = null;
		ResultSet rs = null;
		settleInfoModel s_model = null;
		settleInfoModel[] models = null;
		ArrayList<settleInfoModel> arrList = new ArrayList<settleInfoModel>();
		
		try {
			proc = conn.prepareCall("{ call qry_dealssetle_proc(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
			
			proc.setString(1, deal_id);
			proc.setString(2, ibc); 
			proc.setString(3, cust_name);
			proc.setString(4, ccy); 
			proc.setString(5, cif);
			proc.setString(6, settle_flag);
			proc.setString(7, dateFrom);  //settle today is "" for value_date_fr
			proc.setString(8, dateTo);  //settle today is "" for value_date_to
			proc.registerOutParameter(9, Types.INTEGER);
			
			rs = proc.executeQuery();
			while(rs.next()){
				s_model = new settleInfoModel();
				
				s_model.setCif(rs.getString("cif_number"));
				s_model.setCutName(rs.getString("cust_name"));
				s_model.setIbcCode(rs.getString("ibc_code"));
				s_model.setBranch(rs.getString("branch"));
				s_model.setMaker_name(rs.getString("maker_fname"));
				s_model.setProxy_group_fname(rs.getString("proxy_group_fname"));
				s_model.setProxy_fname(rs.getString("proxy_fname"));
				s_model.setInstrument(rs.getString("instrument_type"));
				s_model.setDealNo(rs.getString("deal_id"));
				s_model.setParentNo(rs.getString("parent_id"));
				s_model.setDealDate(rs.getString("deal_date"));
				s_model.setMatDate(rs.getString("value_date"));
				s_model.setCcy(rs.getString("ccy1"));
				s_model.setBuy_sell(rs.getString("buy_sell"));
				s_model.setExch_rate(rs.getDouble("exch_rate"));
				s_model.setDeal_amt(rs.getDouble("deal_amt1"));
				s_model.setOutstd_amt(rs.getDouble("outstd_amt"));
				
				
				arrList.add(s_model);
			}

			if (arrList.size() > 0) {
				models = (settleInfoModel[]) arrList.toArray(new settleInfoModel[arrList.size()]);
				return models;
			}
				
		} catch (SQLException e) {
			LogWriter.error("1.1 settlementDAO [Method : getInfoDeals] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
			throw new SQLException();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 settlementDAO [Method : getInfoDeals] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 settlementDAO [Method : getInfoDeals] : " + e.getMessage(), Constant.LOGGER_APPLICATION);
					throw new SQLException();
				}
			}
		}
		return models;
	}	
	
	public boolean updateCommentAndByUserOnDealMovement(Connection dcConn,
			String deal_id, String value_date, double settle_amt, String requestId,
			 String src_ref, String comment, String manual_user) throws Exception {
		
		int num_record = 0;
		PreparedStatement ppst = null;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE setdata SET comments = ? , manual_user = ? ");
		sql.append("FROM ( ");
		sql.append("  		SELECT top 1* FROM DEALS_MOVEMENT ");
		sql.append("        WHERE deal_id = ? and  ");
		sql.append("              settle_date = ? and  ");
		sql.append("              settle_amt = ? and  ");
		sql.append("              ws_request_id = ? and  ");
		sql.append("              ws_src_ref = ? and comments is null ");
		sql.append("      ) setdata ");
		
		try {
				ppst = dcConn.prepareStatement(sql.toString());
				
				ppst.setString(1, comment);
				ppst.setString(2, manual_user);
				ppst.setString(3, deal_id); 
				ppst.setString(4, value_date);
				ppst.setDouble(5, settle_amt);			
				ppst.setString(6, requestId);
				ppst.setString(7, src_ref);	
					
				num_record = ppst.executeUpdate();
				dcConn.commit();
				
				if(num_record > 0)
					return true;
			
		} catch (Exception e) {
			LogWriter.error("1.1 settlementDAO [Method : updateCommentOnDealMovement] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 settlementDAO [Method : updateCommentOnDealMovement] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		return false;
	}
	
}
